PC Users 1998 March

home *** CD-ROM | disk | FTP | other *** search

/ PC Users 1998 March / Pc Users extra 6.iso / pshare95 / prog / formula1 / vcform1.z / odbc2.frm (.txt) < prev next >

Wrap

Visual Basic Form | 1997-09-22 | 16.4 KB | 366 lines

VERSION 4.00 Begin VB.Form Form1 Caption = "Double Click On Data in Location Tab for Drill Down" ClientHeight = 5175 ClientLeft = 1365 ClientTop = 1755 ClientWidth = 6600 Height = 5865 Icon = "odbc2.frx":0000 Left = 1305 LinkTopic = "Form1" ScaleHeight = 5175 ScaleWidth = 6600 Top = 1125 Width = 6720 Begin VB.Timer tmrInit Enabled = 0 'False Interval = 500 Left = 4020 Top = 2640 End Begin Threed.SSPanel StatusBar1 Align = 2 'Align Bottom Height = 315 Left = 0 TabIndex = 0 Top = 4860 Width = 6600 _Version = 65536 _ExtentX = 11642 _ExtentY = 556 _StockProps = 15 BackColor = 12632256 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "MS Sans Serif" Size = 8.26 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty BevelOuter = 0 BevelInner = 1 Alignment = 1 End Begin VCF150Ctl.F1Book F1Book1 Height = 4635 Left = 60 TabIndex = 1 Top = 120 Width = 6435 _ExtentX = 11351 _ExtentY = 8176 _0 = $"odbc2.frx":044A _1 = $"odbc2.frx":084F _2 = $"odbc2.frx":0C54 _3 = $"odbc2.frx":1059 _4 = $"odbc2.frx":145E _count = 5 _ver = 1 End Begin VB.Menu mnuFile Caption = "&File" Begin VB.Menu mnuFileExit Caption = "E&xit" End End Begin VB.Menu mnuDataAnalysis Caption = "&Data Analysis" Begin VB.Menu mnuDataAnalysisType Caption = "&Sum" Checked = -1 'True Index = 0 Tag = "SUM" End Begin VB.Menu mnuDataAnalysisType Caption = "&Average" Index = 1 Tag = "Average" End Begin VB.Menu mnuDataAnalysisType Caption = "Standard &Deviation" Index = 2 Tag = "STDEV" End End Begin VB.Menu mnuHelp Caption = "Help!" End Attribute VB_Name = "Form1" Attribute VB_Creatable = False Attribute VB_Exposed = False '' About the tables: '' The ODBC2 database contains three tables: '' Location_Table '' ID Counter '' City Text 50 '' State Text 50 '' Region Text 50 '' Employee_Table '' ID Counter '' First_Name Text 50 '' Last_Name Text 50 '' Location Integer '' Sales_Table '' ID Counter '' Date dd-mmm-yy '' Sales Currency Option Explicit Dim DataSourceName$ ' Initialized in Form Load Dim DataConnectString$ ' Initialized in Form Load Private Sub F1Book1_DblClick(ByVal nRow As Long, ByVal nCol As Long) '' This section implements the drill down aspect of the project. On startup, the '' user will see the contents of the Location_Table. If they double click on a '' column heading, a new sheet is added and a query based on the column header '' is performed and loaded into the new sheet. If the user double clicks on a '' cell, a new sheet is added, a query is made based on the contents of the cell '' and placed in the sheet. Dim query$, tabName$, funcNum%, i% '' The queries look complex but were created in a minute by opening the ODBC2.MDB '' in Access, creating the query with the query builder, switching to SQL mode, '' and copying the result here. Although predefined queries would be faster, they '' would require much more work and be much less flexible. First you would have '' to generate queries for each item you would allow the use to drill on. Next '' you would have to get the item the user clicked on and somehow transform that '' into the name of the query you generated. Next you have to worry about records '' being deleted, added, or modified. What happens if you add an employee or '' location? Do you create new queries? Do you have to modify your code?. '' The method used here avoids all this extra work at a relatively small '' performance penalty. '' '' How to make the queries: '' We know the tables as defined above. We also know that the first table they '' see is the Location_Table so to start with, lets give the user a crosstab '' that shows area versus date with sum of sales as the data. We do a bottom up '' design and evolve it to fit our needs. Lets say the user clicks on the column '' header for the city field in the Location_Table. To handle this case, first build '' a crosstab in Access for city vs date and copy it here. This will handle the '' case of the user double clicking in the City column header. If we replace single '' occurance of city with the column header text the user clicked on (with ColText '' method) we have a more versatile query that will fetch by city, state, or region. '' '' Now Evolve: '' The above will generate 3 queries, but what if the user wants data for an '' individual city. As a response, we can stick with the crosstab theme and provide '' sales by person. Back to Access to build another query. We make this a crosstab '' that shows Last_Name in the columns, date in rows, sum of sales as data, all '' for the city of New Haven. Switch to SQL mode and copy the text here. For this '' query we replace all occurances of City with the text from the column of the '' cell the user clicked on and the occurance of New Haven with the text from the '' cell the user double clicked on (TextRC method). '' '' SPECIAL NOTE: you use SINGLE QUOTES for a string in this query where Access '' uses double quotes. '' '' How to tell which query to run? Simply check which row was double clicked on. '' If nRow = 0, it was a column header so use the first query. Otherwise use the '' second query. '' '' So now, with two "parameterized" queries in code below, we can generate 29 '' different sales crosstabs. Not too bad for this little function. '' '' Error Checking: '' Of course we want some limit to the havoc the user can play with this simple '' demo, so we limit the queries to double clicks in the data cells or the '' column headers over the data cells. With F1Book1 StatusBar1.Caption = "Adding New Sheet..." StatusBar1.Refresh If .Sheet = 1 Then If nRow <= .LastRow And nCol > 0 And nCol <= .LastCol Then '' Crosstab of sales by City, State, Region if click on col header If (nRow = 0) Then Let query = "TRANSFORM Sum(Sales_Table.Sales) AS SumOfSales " & _ "SELECT Sales_Table.Date FROM Sales_Table INNER JOIN " & _ "(Employee_Table INNER JOIN Location_Table ON " & _ "Employee_Table.Location = Location_Table.ID) ON " & _ "Sales_Table.Employee = Employee_Table.ID GROUP BY " & _ "Sales_Table.Date PIVOT Location_Table." & .ColText(nCol) & ";" Let tabName = "Sales By " & .ColText(nCol) .SetSelection 1, nCol, 1, nCol StatusBar1.Caption = "Fetching Data for " & .ColText(nCol) & "..." Else '' Crosstab of area if clicked on cell Let query = "TRANSFORM Sum(Sales_Table.Sales) AS SumOfSales " & _ "SELECT Sales_Table.Date FROM Sales_Table INNER JOIN " & _ "(Employee_Table INNER JOIN Location_Table ON Employee_Table.Location " & _ "= Location_Table.ID) ON Sales_Table.Employee = Employee_Table.ID " & _ "WHERE ((Location_Table." & .ColText(nCol) & " = '" & _ .TextRC(nRow, nCol) & "')) GROUP BY Sales_Table.Date, " & _ "Location_Table." & .ColText(nCol) & " PIVOT Employee_Table.Last_Name;" Let tabName = "Sales In " & .TextRC(nRow, nCol) StatusBar1.Caption = "Fetching Data for " & .TextRC(nRow, nCol) & "..." End If StatusBar1.Refresh '' Add a sheet, make it active, set the tab name .InsertSheets .NumSheets + 1, 1 .Sheet = .NumSheets .SheetName(.Sheet) = tabName Call Fetch(F1Book1, .Sheet, 1, 1, DataConnectString, query, True, True, True, False) '' Need to determine which data analysis to use funcNum = 0 For i = 0 To 2 If mnuDataAnalysisType(i).Checked Then funcNum = i Next i StatusBar1.Caption = "Calculating Summary info and formatting..." StatusBar1.Refresh Call SetRowColCalc(F1Book1, mnuDataAnalysisType(funcNum).Tag, 1, .LastRow, 2, .LastCol) '' LastCol is now the sum column Call NameAndFormatColumn(F1Book1, .Sheet, .LastCol, "Total Sales", "$#,##0.00") Call NameAndFormatColumn(F1Book1, .Sheet, 1, .ColText(1), "dd-mmm-yy") Call NameAndFormatRow(F1Book1, .Sheet, .LastRow, "Total Sales", "$#,##0.00") Call FormatSalesCrossTab(F1Book1, .Sheet) End If End If StatusBar1.Caption = "Ready..." End With End Sub Private Sub Form_Load() Let DataSourceName = "FO_ODBC2" '' See Accessing External Databases in the VB4 help for '' more info on creating this string (Professional Version). '' When using this string in the ODBCConnect method, Formula '' One will prompt for any information that is not provided by '' either the data source or the string. For this project we '' set all information for the data source with SQLConfigDataSource '' and just tell Formula One to use ODBC and the data src name. '' '' These are done in a timer so the form can be refreshed and '' the load time doesn't look so long. Two things take a while '' to do - registering the database and getting an open '' connection to it. After that everything is quite speedy. '' You will probably want to hide this activity in a timer '' also. The user will probably stare at the screen for three '' or four seconds before starting anyway. Put up a help '' screen or introduction to keep them busy. Let DataConnectString = "ODBC;DSN=" & DataSourceName '' Now start the timer for the help and the DSN and fetch 'tmrHelp.Enabled = True tmrInit.Enabled = True End Sub Private Sub Form_Resize() F1Book1.Width = ScaleWidth F1Book1.Height = ScaleHeight - StatusBar1.Height End Sub Private Sub mnuDataAnalysisType_Click(Index As Integer) '' Indices are '' 0 Sum '' 1 Average '' 2 Standard Deviation Dim i% For i = 0 To 2 mnuDataAnalysisType(i).Checked = False Next i mnuDataAnalysisType(Index).Checked = True If F1Book1.Sheet <> 1 Then Call SetRowColCalc(F1Book1, mnuDataAnalysisType(Index).Tag, _ 1, F1Book1.LastRow - 1, 2, F1Book1.LastCol - 1) Call FormatSalesCrossTab(F1Book1, F1Book1.Sheet) End If End Sub Private Sub mnuFileExit_Click() End End Sub Private Sub mnuHelp_Click() Dim msg$ StatusBar1.Caption = "Helping..." Let msg = "This data drill down example constructs queries on the fly " & _ "based on a double click on the spreadsheet with the tab name Location. " & _ "For example, double clicking on the column header named city will generate " & _ "a crosstab that shows sales in all cities over dates. A double click on " & _ "a cell containing New Haven will generate a query for all sales staff in " & _ "New Haven over dates covered in the database. " & Chr$(13) & Chr$(13) & _ "A new sheet is added to the workbook and filled with the query results. " & _ "The bottom row and right column contain simple data analysis whose default " & _ "is a sum. You can change this analysis for the active sheet and all future " & _ "by making a selection in Data Analysis menu." MsgBox msg StatusBar1.Caption = "Ready..." End Sub Private Sub tmrInit_Timer() Dim dbDriver$, dbAttributes$ Dim result As Boolean tmrInit.Enabled = False MousePointer = 11 '' The VB method DBEngine.RegisterDatabase will register '' the data source name but not the database name or system '' database name. There are four choices for doing this: Let '' the user figure it out at run time, Write to the INI and '' Registry yourself, Configure the data source ahead of time '' with the ODBC admin 32 program, or use DLL calls as shown '' in the example below. One warning - the VB DBEngine methods '' are also very slow compared to the example below. '' The following is left in as a curiosity... '' Adds an entry to the ODBC.INI file and registry. Should be '' done one time prior to use. Can be called every time '' and only one entry will be made, but this slows load '' time and destroys the settings for an existing '' datasource that are made with the ODBC admin '' 32 program (such as database name, system.mda). 'DBEngine.IniPath = App.Path & "\ODBC2.INI" 'DBEngine.RegisterDatabase DataSourceName, _ "Microsoft Access Driver (*.mdb) (32 bit)", _ True, "Driver32=C:\WINNT35\System32\odbcjt32.dll" '' The following sets up a Data Source name and all the '' attributes that are necessary to connect to the data '' source without showing a dialog. The DSN is reconfigured '' each time but this is pretty fast. The attributes must '' be in null terminated keyword-value pairs with two nulls '' at the end of the string. '' Passing Form1.hWnd will generate a dialog for the user to '' modify the info provided but, if you want the user to do '' this, you can just use the Formula One generated dialogs '' to create a new data source and skip all the dll stuff. '' See odbc2.bas for more info. StatusBar1.Caption = "Registering New Database..." StatusBar1.Refresh '' SQL Config DataSource use the text name to find the driver. '' These are what my 32/16 bit drivers are installed as - yours '' may have some slightly different name depending on where you '' got them. Check the ODBC Admin program to find the name. #If Win32 Then Let dbDriver = "Microsoft Access Driver (*.mdb)" #ElseIf Win16 Then Let dbDriver = "Access Files (*.mdb)" #End If Let dbAttributes = "DSN=" & DataSourceName & Chr$(0) _ & "DBQ=" & App.Path & "\ODBC2.MDB" & Chr$(0) _ & "SystemDB=" & App.Path & "\system.mda" & Chr$(0) _ & "DefaultDir=" & App.Path & Chr$(0) _ & "UID=admin" & Chr$(0) _ & "PWD=" & Chr$(0) & Chr$(0) '' If the next statement fails, we can't get to the database without '' the user going through some hoops, so we exit. result = SQLCONFIGDATASOURCE(0, ODBC_ADD_DSN, dbDriver, dbAttributes) If (False = result) Then MsgBox "Establishing DSN failed! Error: " & result End End If StatusBar1.Caption = "Fetching Initial Table..." StatusBar1.Refresh '' Startup with the Location Table Call Fetch(F1Book1, 1, 1, 1, DataConnectString, _ "Select City, State, Region From Location_Table", True, True, True, True) Call MakeYellowGreenBars(F1Book1, 1) With F1Book1 .Left = 0 .Top = 0 .SetSelection 1, 1, .LastRow, .LastCol .SetBorder 5, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 .Selection = "A1" .SheetName(1) = "Locations" End With StatusBar1.Caption = "Ready..." StatusBar1.Refresh MousePointer = 0 End Sub